use pid_pde e1ddpsta date_snapshot using "$data_in/personnel", clear
gen date_decision = dofc(date_snapshot)
drop date_snapshot
rename e1ddpsta clearance_code
gen clearance_suspended = clearance_code == "M"
gen clearance_denied = inlist(clearance_code, "L", "N", "P")
gen clearance_granted = inrange(clearance_code,"A", "G")

drop if clearance_suspended == 0 & clearance_denied == 0 & clearance_granted == 0
drop clearance_code
bys pid: gen n = _n

summ n 
local max_r `r(max)'
reshape wide date_decision clearance*, i(pid_pde) j(n)

merge 1:m pid_pde using "$data_out/provider_propensity", keep(2 3) nogen

keep if officer == 0 & service == "A"

gen revoke = 0
gen denied = 0
gen granted = 0
gen revoke2 = 0
gen denied2 = 0
gen granted2 = 0

forval i = 1/`max_r' {
qui replace revoke = clearance_suspended`i' if inrange(date_decision`i', encdate, encdate + 365) & date_decision`i' <. & revoke == 0
qui replace denied = clearance_denied`i' if inrange(date_decision`i', encdate, encdate + 365) & date_decision`i' <. & denied == 0
qui replace granted = clearance_granted`i' if inrange(date_decision`i', encdate, encdate + 365) & date_decision`i' <. & granted == 0
qui replace revoke2 = clearance_suspended`i' if inrange(date_decision`i', encdate, encdate + 730) & date_decision`i' <. & revoke2 == 0
qui replace denied2 = clearance_denied`i' if inrange(date_decision`i', encdate, encdate + 730) & date_decision`i' <. & denied2 == 0
qui replace granted2 = clearance_granted`i' if inrange(date_decision`i', encdate, encdate + 730) & date_decision`i' <. & granted2 == 0

}

save "$data_out/Clearances", replace

